# _*_ coding:utf-8 _*_
# @Time  : 2023.03.20
# @Author: zizlee
import datetime
import pathlib
from collections import Counter
from fastapi import APIRouter, Header, Form, UploadFile, Query, Body
from pydantic import BaseModel, validator
from hutool import security
from v1_all_api.all_response import AllResponse
from v1_all_api.all_utils import file_utils, datetime_utils, char_utils
from db import FAConnection
from configs import STATIC_FILE_HOST, FILE_STORAGE
from . import work_utils

industrial_service_api = APIRouter()


# 管理查询产业服务案例
@industrial_service_api.get('/')
def query_industrial_service(ruizy_token: str = Header(...), page: int = Query(1), page_size:int = Query(30)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if not security.operator_is_manager(person):
        return AllResponse.forbidden_error()
    sql = """
        SELECT a.*,u.admin_name FROM work_industrial_service As a 
        INNER JOIN ruizy_user As u ON a.user_id=u.id AND is_active=1 ORDER BY start_date DESC;
    """
    db_conn = FAConnection()
    ret = db_conn.query_paginator(sql, param=[], page=page, page_size=page_size)
    for item in ret['data']:
        item['annex_url'] = STATIC_FILE_HOST + item['annex_url']
        item['create_time'] = item['create_time'].strftime('%Y-%m-%d %H:%M:%S')
    return AllResponse.operate_successfully(data=ret)


# 管理删除功能我的产业服务案例
@industrial_service_api.delete('/')
def delete_industrial_service(ruizy_token: str = Header(...), rid: int = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if not security.operator_is_manager(person):
        return AllResponse.forbidden_error()
    # 查询出文件，删除记录，删除文件
    with FAConnection() as cursor:
        cursor.execute('SELECT id,annex,annex_url,create_time FROM work_industrial_service WHERE id=%s LIMIT 1;', [rid])
        obj = cursor.fetchone()
        if not obj:
            return AllResponse.validate_error(msg='要删除的记录不存在!')
        annex_file = pathlib.Path(FILE_STORAGE).joinpath(obj['annex_url'])
        c = cursor.execute('DELETE FROM work_industrial_service WHERE id=%s LIMIT 1;', [rid])
        if c > 0 and annex_file.exists() and annex_file.is_file():
            annex_file.unlink()  # 删除文件
    return AllResponse.operate_successfully(msg='删除成功!')


# 评级人员查询记录供评级
@industrial_service_api.get('/level/')
def get_industrial_service_level(ruizy_token: str = Header(...), date: int = Query(None), level: int = Query(0),
                                page: int = Query(1), page_size: int = Query(4)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    sql = """
        SELECT a.*,u.admin_name FROM work_industrial_service As a 
        INNER JOIN ruizy_user As u ON a.user_id=u.id
        WHERE a.is_active=1
    """
    prm = []
    if level:
        sql += " AND a.level_flag<>''"
    else:
        sql += " AND a.level_flag=''"
    if date:
        if not datetime_utils.auth_datetime_string(str(date), f='%Y%m%d'):
            return AllResponse.validate_error(msg='查询参数错误')
        sql += ' AND DATE_FORMAT(a.create_time,"%%Y%%m%%d")=%s ORDER BY a.create_time DESC;'
        prm.append(date)
    else:
        sql += ' ORDER BY a.create_time DESC;'
    db_conn = FAConnection()
    ret = db_conn.query_paginator(sql, prm, page=page, page_size=page_size)
    for item in ret['data']:
        item['create_time'] = datetime_utils.to_string(item['create_time'], is_time=True)
        item['annex_url'] = STATIC_FILE_HOST + item['annex_url']
    return AllResponse.operate_successfully(data=ret)


class LevelItem(BaseModel):
    rid: int
    level_flag: str

    @validator('level_flag')
    def validate_level_flag(cls, value):
        if value not in ['A', 'B', 'C', 'D', 'E']:
            raise ValueError('等级只能为A、B、C、D、E其中一个')
        return value


# 评级人员评级
@industrial_service_api.put('/level/')
def put_industrial_service_level(ruizy_token: str = Header(...), row: LevelItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    with FAConnection() as cursor:
        cursor.execute("UPDATE work_industrial_service SET level_flag=%s WHERE id=%s LIMIT 1;",
                       [row.level_flag, row.rid])
    return AllResponse.operate_successfully(msg='评级成功!')


# ----------------------------------------------------------------------------------------------------------------------

# 创建我的产业服务案例
@industrial_service_api.post('/my/')
async def save_my_industrial_service(ruizy_token: str = Header(...), start_date: str = Form(...), end_date: str = Form(''),
                                     company_name: str = Form(...), company_locate: str = Form(...), company_area: str = Form(...),
                                     company_area_note: str = Form(''), company_type: str = Form(...), company_type_note: str = Form(''),
                                     service_model: str = Form(...), service_model_note: str = Form(''), varieties: str = Form(...),
                                     summary: str = Form(''), annex: UploadFile = Form(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if not datetime_utils.auth_datetime_string(start_date, f='%Y-%m-%d'):
        return AllResponse.validate_error(msg='日期格式错误，接受YYYY-MM-DD类型')
    end_date = datetime_utils.auth_datetime_string(end_date, f='%Y-%m-%d')
    # 保存文件
    save_path, sql_path = file_utils.get_file_paths(module_name='IndustrialService', filename=annex.filename,
                                                    hashed=True)
    # 涉及品种是开放填写的，将字符都转为半角
    varieties = char_utils.strQ2B(varieties)
    file_content = await annex.read()  # 将文件保存到目标位置
    with open(save_path, "wb") as fp:
        fp.write(file_content)
    # 保存记录
    sql = """
        INSERT INTO work_industrial_service (user_id,start_date,end_date,company_name,company_locate,
        company_area,company_area_note,company_type,company_type_note,service_model,service_model_note,varieties,
        summary,annex,annex_url)
        VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);
    """
    prm = [person['uid'], start_date, end_date, company_name.strip(), company_locate.strip(), company_area.strip(),
           company_area_note.strip(), company_type.strip(), company_type_note.strip(), service_model.strip(),
           service_model_note.strip(), varieties.strip(), summary.strip(), annex.filename.strip(), sql_path]
    with FAConnection() as cursor:
        cursor.execute(sql, prm)
        # 本条记录的id
        cursor.execute('SELECT LAST_INSERT_ID() AS insertID;')
        insert_id = cursor.fetchone()['insertID']
        if insert_id < 1:
            # 删除文件
            img = pathlib.Path(save_path)
            if img.exists() and img.is_file():
                img.unlink(missing_ok=True)
            return AllResponse.server_error(msg='服务器错误,保存失败...')
    return AllResponse.operate_successfully(msg='保存服务案例成功!')


# 查询我的产业服务案例
@industrial_service_api.get('/my/')
def query_my_industrial_service(ruizy_token: str = Header(...), page: int = Query(1), page_size:int = Query(30)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()

    sql = """
        SELECT * FROM work_industrial_service WHERE user_id=%s AND is_active=1 ORDER BY start_date DESC;
    """
    db_conn = FAConnection()
    ret = db_conn.query_paginator(sql, param=[person['uid']], page=page, page_size=page_size)
    for item in ret['data']:
        item['annex_url'] = STATIC_FILE_HOST + item['annex_url']
    return AllResponse.operate_successfully(data=ret)


# 删除功能我的产业服务案例
@industrial_service_api.delete('/my/')
def delete_my_industrial_service(ruizy_token: str = Header(...), rid: int = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    # 查询出文件，删除记录，删除文件
    with FAConnection() as cursor:
        cursor.execute('SELECT id,annex,annex_url,create_time FROM work_industrial_service WHERE id=%s AND user_id=%s LIMIT 1;',
                       [rid, person['uid']])
        obj = cursor.fetchone()
        if not obj:
            return AllResponse.validate_error(msg='要删除的记录不存在!')
        if (datetime.datetime.today() - obj['create_time']).days >= 30:
            return AllResponse.validate_error(msg='较早的记录不支持删除了!')
        annex_file = pathlib.Path(FILE_STORAGE).joinpath(obj['annex_url'])
        c = cursor.execute('DELETE FROM work_industrial_service WHERE id=%s AND user_id=%s LIMIT 1;',
                           [rid, person['uid']])
        if c > 0 and annex_file.exists() and annex_file.is_file():
            # 删除文件
            annex_file.unlink()
    return AllResponse.operate_successfully(msg='删除成功!')


# 产业服务案例统计
@industrial_service_api.get('/statistics/')
def statistics_industrial_service(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                                 st: int = Query(0, ge=0, le=1)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='param start and end format error!')
    is_admin = security.operator_is_manager(person)
    only_view_team = True if st else False
    members, is_leader = work_utils.get_operator_group_members(user_id=person['uid'], query_date=start_date)
    # 查询目标区间的数据
    sql = """
        SELECT a.*,b.admin_name 
        FROM work_industrial_service As a
        INNER JOIN ruizy_user As b ON a.user_id=b.id 
        WHERE DATE_FORMAT(a.create_time,"%%Y%%m%%d")>=%s AND DATE_FORMAT(a.create_time,"%%Y%%m%%d")<=%s AND is_active=1
        ORDER BY create_time DESC;
    """
    db_conn = FAConnection()
    records = db_conn.query(sql, param=(start_date, end_date))

    # 查看的数据控制
    if is_admin:  # 管理员可以统计所有人或组员
        if only_view_team:  # 只查看组员
            members_id = [m['user_id'] for m in members]  # 组员id
            records = list(filter(lambda x: x['user_id'] in members_id, records))
    else:  # 不是管理员，只能查自己或组员
        members_id = [person['uid']]
        if only_view_team and is_leader:
            members_id = [m['user_id'] for m in members]  # 组员id
        records = list(filter(lambda x: x['user_id'] in members_id, records))
    # 对记录处理和各人员数据进行处理
    user_dict = {}
    for item in records:
        item['create_time'] = datetime_utils.to_string(item['create_time'], is_time=True)
        item['annex_url'] = STATIC_FILE_HOST + item['annex_url']
        no_level = 0
        if not item['level_flag']:
            no_level = 1
        if item['user_id'] in user_dict.keys():
            _o = user_dict[item['user_id']]
            _o['data'].append(item)
            _o['count'] += 1
            _o['no_level'] += no_level
        else:
            user_dict[item['user_id']] = {
                'user_id': item['user_id'],
                'admin_name': item['admin_name'],
                'data': [item],
                'count': 1,
                'no_level': no_level
            }
    # 展开数据为列表，并计算评级
    statistics_data = []
    total_levels = []
    total_obj = {'user_id': -1, 'admin_name': '合计', 'data': records, 'count': len(records), 'level_count': ''}
    for _, v in user_dict.items():
        # 评级
        levels = [i['level_flag'] for i in v['data'] if i['level_flag']]
        levels.sort()  # 排序ABC
        total_levels.extend(levels)
        v['level_count'] = ''.join(['%d%s' % (v, k) for k, v in Counter(levels).items() if k])
        statistics_data.append(v)
    statistics_data.sort(key=lambda x: x['count'], reverse=True)
    # 加入合计
    if len(statistics_data) > 0:
        total_levels.sort()
        total_obj['level_count'] = ''.join(['%d%s' % (v, k) for k, v in Counter(total_levels).items() if k])
        total_obj['no_level'] = sum([u['no_level'] for u in statistics_data])
        statistics_data.append(total_obj)
    return AllResponse.operate_successfully(data={'statistics': statistics_data, 'statistics_tip': '注：总数量包含未评级的条目。'})
